from datetime import datetime

import pymysql
from pymysql import Connection

class ProductManagementSystem:
    def __init__(self):
        # self.conn = None
        self.conn = self.connect()
        self.init_tables() # 创建表
        self.init_categories() #初始化数据类别


    def connect(self):
        """
        功能：用于获取数据库连接对象的方法
        :return: 返回数据库连接对象
        """
        self.conn = Connection(
            host='localhost',
            port=3306,
            user='root',
            password='root',
            database='db05',
            autocommit=True,
            cursorclass = pymysql.cursors.DictCursor  # 配置游标返回字典格式
        )
        return self.conn

    def close(self):
        """
        功能：释放资源
        """
        if self.conn:
           try:
               self.conn.close()
           except Exception as e:
               print('数据库关闭异常',e)
           finally:
               self.conn = None

    # 析构方法 定义了在进入 with 语句块时要执行的操作，通常用于资源分配或初始化工作。
    def __enter__(self):
        # 创建数据库连接
        self.connect()
        return self


    # 与 __enter__ 方法一起实现 with 语句的功能。它主要负责资源清理和异常处理。
    def __exit__(self, exc_type, exc_val, exc_tb):
        self.close()
        return False

    def init_tables(self):
        """初始化数据库表结构"""
        try:
            with self.conn.cursor() as cursor:
                # 创建商品类别表
                sql = """
                    CREATE TABLE IF NOT EXISTS categories(
                        category_id INT PRIMARY KEY AUTO_INCREMENT,
                        category_name VARCHAR(50) NOT NULL
                    )
                """
                cursor.execute(sql)
                # 创建商品表
                sql2 = """
                    CREATE TABLE IF NOT EXISTS products(
                        product_id INT PRIMARY KEY AUTO_INCREMENT,
                        category_id INT NOT NULL,
                        product_name VARCHAR(100) NOT NULL,
                        barcode VARCHAR(20) UNIQUE,
                        production_date DATE,
                        price DECIMAL(10,2) NOT NULL,
                        stock INT NOT NULL DEFAULT 0,
                        FOREIGN KEY(category_id) REFERENCES categories(category_id)
                    )
                """
                cursor.execute(sql2)
        except Exception as e:
            raise e

    def init_categories(self):
        """初始化商品类别数据"""
        categories = ["电子产品", "食品饮料", "服装鞋帽", "家居用品", "图书文具"]
        with self.conn.cursor() as cursor:
            # 检查是否已有数据
            cursor.execute("SELECT COUNT(*) AS count FROM categories")
            result = cursor.fetchone()
            if result['count'] == 0:
                for category in categories:
                    sql = "INSERT INTO categories (category_name) VALUES (%s)"
                    cursor.execute(sql,(category,))
                print("成功初始化商品类别数据")

    def show_menu(self):
        """显示主菜单"""
        while True:
            print("\n=== 商品管理系统 ===")
            print("1. 浏览所有商品")
            print("2. 添加新商品")
            print("3. 修改商品价格")
            print("4. 查询库存最多的商品")
            print("5. 根据类别查询商品")
            print("6. 根据价格范围查询")
            print("7. 根据生产日期范围查询")
            print("8. 根据商品名称模糊查询")
            print("0. 退出系统")
            choice = input("请选择操作(0-8): ")
            if choice == '1':
                self.list_all_products()
            elif choice == '2':
                self.add_product()
            elif choice == '3':
                self.update_product_price()
            elif choice == '4':
                self.find_max_stock_product()
            elif choice == '5':
                self.query_by_category()
            elif choice == '6':
                self.query_by_price_range()
            elif choice == '7':
                self.query_by_date_range()
            elif choice == '8':
                self.query_by_name()
            elif choice == '0':
                print("感谢使用商品管理系统，再见！")
                self.conn.close()
                break
            else:
                print("无效的选择，请重新输入！")

    def list_all_products(self):
        """功能1: 浏览所有商品"""
        try:
            with self.conn.cursor() as cursor:
                sql = """
                     SELECT 
                        p.*, c.category_name
                    FROM 
                        products p
                    JOIN 
                        categories c 
                    ON 
                        p.category_id = c.category_id
                """
                cursor.execute(sql)
                products = cursor.fetchall()
                if not products:
                    print("当前没有商品记录")
                    return
                print("\n=== 所有商品列表 ===")
                print(f"{'ID':<5}{'类别':<10}{'名称':<20}{'条码':<15}{'生产日期':<12}{'价格':<10}{'库存':<10}")
                print("-" * 80)
                for product in products:
                    print(f"{product['product_id']:<5}"
                          f"{product['category_name']:<10}"
                          f"{product['product_name']:<20}"
                          f"{product['barcode'] or '无':<15}"
                          f"{str(product['production_date'] or '未知'):<12}"
                          f"{product['price']:<10.2f}"
                          f"{product['stock']:<10}")
        except Exception as e:
            print(f"查询商品列表出错: {e}")

    def add_product(self):
        """功能2: 添加新商品"""
        try:
            # 先显示所有类别供用户选择
            with self.conn.cursor() as cursor:
                cursor.execute("SELECT * FROM categories")
                categories = cursor.fetchall()
                if not categories:
                    print("没有可用的商品类别，请先添加类别")
                    return
                print("\n=== 可用的商品类别 ===")
                for cat in categories:
                    print(f"{cat['category_id']}. {cat['category_name']}")
                # 获取用户输入
                category_id = input("请选择类别ID: ")
                product_name = input("请输入商品名称: ")
                barcode = input("请输入商品条码(可选): ")
                production_date = input("请输入生产日期(YYYY-MM-DD，可选): ")
                price = input("请输入商品价格: ")
                stock = input("请输入库存数量(默认为0): ")
                # 验证输入
                try:
                    category_id = int(category_id)
                    price = float(price)
                    stock = int(stock) if stock else 0
                    if production_date:
                        datetime.strptime(production_date, "%Y-%m-%d")
                except ValueError:
                    print("输入格式不正确，请检查!")
                    return
                # 插入数据
                sql = """
                      INSERT INTO products(
                          category_id, product_name, 
                          barcode, production_date, 
                          price, stock)
                      VALUES (%s, %s, %s, %s, %s, %s)
                """
                cursor.execute(sql, (
                    category_id, product_name, barcode or None,
                    production_date or None, price, stock
                ))
                self.conn.commit()
                print("商品添加成功!")
        except pymysql.err.IntegrityError as e:
            print(f"添加失败: 条码必须唯一或类别ID不存在")
        except Exception as e:
            print(f"添加商品出错: {e}")

    def update_product_price(self):
        """功能3: 修改商品价格"""
        try:
            product_id = input("请输入要修改的商品ID: ")
            new_price = input("请输入新的价格: ")
            try:
                product_id = int(product_id)
                new_price = float(new_price)
            except ValueError:
                print("ID和价格必须是数字")
                return
            with self.conn.cursor() as cursor:
                # 检查商品是否存在
                cursor.execute("SELECT * FROM products WHERE product_id = %s", (product_id,))
                if not cursor.fetchone():
                    print("该商品ID不存在")
                    return
                # 更新价格
                cursor.execute(
                    "UPDATE products SET price = %s WHERE product_id = %s",
                    (new_price, product_id)
                )
                print("商品价格更新成功!")
        except Exception as e:
            print(f"修改价格出错: {e}")

    def find_max_stock_product(self):
        """功能4: 查询库存最多的商品"""
        try:
            with self.conn.cursor() as cursor:
                sql = """
                    SELECT 
                        p.*, c.category_name
                    FROM 
                        products p
                    JOIN 
                        categories c 
                    ON 
                        p.category_id = c.category_id
                    ORDER BY 
                        p.stock DESC 
                    LIMIT 1   
                """
                cursor.execute(sql)
                product = cursor.fetchone()
                if not product:
                    print("当前没有商品记录")
                    return
                print("\n=== 库存最多的商品 ===")
                print(f"商品ID: {product['product_id']}")
                print(f"商品名称: {product['product_name']}")
                print(f"类别: {product['category_name']}")
                print(f"条码: {product['barcode'] or '无'}")
                print(f"生产日期: {product['production_date'] or '未知'}")
                print(f"价格: {product['price']:.2f}")
                print(f"库存: {product['stock']}")
        except Exception as e:
            print(f"查询库存最多商品出错: {e}")

    def query_by_category(self):
        """功能5: 根据类别查询商品"""
        try:
            # 显示所有类别
            with self.conn.cursor() as cursor:
                cursor.execute("SELECT * FROM categories")
                categories = cursor.fetchall()
                if not categories:
                    print("没有可用的商品类别")
                    return
                print("\n=== 可用的商品类别 ===")
                for cat in categories:
                    print(f"{cat['category_id']}. {cat['category_name']}")
                category_id = input("请选择要查询的类别ID: ")
                try:
                    category_id = int(category_id)
                except ValueError:
                    print("类别ID必须是数字")
                    return
                # 查询该类别下的商品
                sql = """
                  SELECT 
                        p.*, c.category_name
                  FROM 
                      products p
                  JOIN 
                      categories c 
                  ON 
                      p.category_id = c.category_id
                  WHERE 
                      p.category_id = %s   
                """
                cursor.execute(sql, (category_id,))
                products = cursor.fetchall()
                if not products:
                    print("该类别下没有商品")
                    return
                print(f"\n=== 类别 {categories[category_id - 1]['category_name']} 的商品 ===")
                print(f"{'ID':<5}{'名称':<20}{'条码':<15}{'生产日期':<12}{'价格':<10}{'库存':<10}")
                print("-" * 70)
                for product in products:
                    print(f"{product['product_id']:<5}"
                          f"{product['product_name']:<20}"
                          f"{product['barcode'] or '无':<15}"
                          f"{str(product['production_date'] or '未知'):<12}"
                          f"{product['price']:<10.2f}"
                          f"{product['stock']:<10}")
                # 计算总价值
                cursor.execute("""
                               SELECT SUM(price * stock) AS total_value
                               FROM 
                                   products
                               WHERE 
                                   category_id = %s
                               """, (category_id,))
                total = cursor.fetchone()['total_value'] or 0
                print(f"\n该类别商品总价值: {total:.2f}元")
        except Exception as e:
            print(f"按类别查询出错: {e}")

    def query_by_price_range(self):
        """功能6: 根据价格范围查询"""
        try:
            min_price = input("请输入最低价格: ")
            max_price = input("请输入最高价格: ")
            try:
                min_price = float(min_price)
                max_price = float(max_price)
            except ValueError:
                print("价格必须是数字")
                return
            with self.conn.cursor() as cursor:
                sql = """
                      SELECT 
                          p.*, c.category_name
                      FROM 
                          products p
                      JOIN 
                          categories c 
                      ON 
                          p.category_id = c.category_id
                      WHERE 
                          p.price BETWEEN %s AND %s
                      ORDER BY 
                          p.price 
                """
                cursor.execute(sql, (min_price, max_price))
                products = cursor.fetchall()
                if not products:
                    print(f"没有价格在 {min_price}-{max_price} 之间的商品")
                    return
                print(f"\n=== 价格在 {min_price}-{max_price} 之间的商品 ===")
                print(f"{'ID':<5}{'类别':<10}{'名称':<20}{'价格':<10}{'库存':<10}")
                print("-" * 55)
                for product in products:
                    print(f"{product['product_id']:<5}"
                          f"{product['category_name']:<10}"
                          f"{product['product_name']:<20}"
                          f"{product['price']:<10.2f}"
                          f"{product['stock']:<10}")
        except Exception as e:
            print(f"按价格范围查询出错: {e}")

    def query_by_date_range(self):
        """功能7: 根据生产日期范围查询"""
        try:
            start_date = input("请输入开始日期(YYYY-MM-DD): ")
            end_date = input("请输入结束日期(YYYY-MM-DD): ")
            try:
                datetime.strptime(start_date, "%Y-%m-%d")
                datetime.strptime(end_date, "%Y-%m-%d")
            except ValueError:
                print("日期格式不正确，请使用YYYY-MM-DD格式")
                return
            with self.conn.cursor() as cursor:
                sql = """
                      SELECT 
                          p.*, c.category_name
                      FROM 
                          products p
                      JOIN 
                          categories c 
                      ON 
                          p.category_id = c.category_id
                      WHERE 
                          p.production_date BETWEEN %s AND %s
                      ORDER BY 
                          p.production_date 
                """
                cursor.execute(sql, (start_date, end_date))
                products = cursor.fetchall()
                if not products:
                    print(f"没有在 {start_date} 到 {end_date} 之间生产的商品")
                    return
                print(f"\n=== 生产日期在 {start_date} 到 {end_date} 之间的商品 ===")
                print(f"{'ID':<5}{'类别':<10}{'名称':<20}{'生产日期':<12}{'价格':<10}")
                print("-" * 60)
                for product in products:
                    print(f"{product['product_id']:<5}"
                          f"{product['category_name']:<10}"
                          f"{product['product_name']:<20}"
                          f"{str(product['production_date']):<12}"
                          f"{product['price']:<10.2f}")
        except Exception as e:
            print(f"按日期范围查询出错: {e}")

    def query_by_name(self):
        """功能8: 根据商品名称模糊查询"""
        try:
            keyword = input("请输入商品名称关键字: ")
            with self.conn.cursor() as cursor:
                sql = """
                      SELECT 
                          p.*, c.category_name
                      FROM 
                          products p
                      
                      JOIN 
                          categories c 
                      ON 
                          p.category_id = c.category_id
                      WHERE 
                          p.product_name LIKE %s
                      """
                cursor.execute(sql, (f"%{keyword}%",))
                products = cursor.fetchall()
                if not products:
                    print(f"没有名称包含 '{keyword}' 的商品")
                    return
                print(f"\n=== 名称包含 '{keyword}' 的商品 ===")
                print(f"{'ID':<5}{'类别':<10}{'名称':<20}{'价格':<10}{'库存':<10}")
                print("-" * 55)
                for product in products:
                    print(f"{product['product_id']:<5}"
                          f"{product['category_name']:<10}"
                          f"{product['product_name']:<20}"
                          f"{product['price']:<10.2f}"
                          f"{product['stock']:<10}")
        except Exception as e:
            print(f"按名称查询出错: {e}")


if __name__ == "__main__":
    with ProductManagementSystem() as system:
        try:
            system.show_menu()
        except pymysql.err.OperationalError as e:
            print(f"数据库连接失败: {e}")
        except Exception as e:
            print(f"系统错误: {e}")
